package com.augurit.gzcgwsp.ywcmn.service.impl;

import java.math.BigDecimal;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;
import org.springside.modules.orm.PropertyFilter;

import com.augurit.common.util.PageUtils;
import com.augurit.common.util.lang.DateUtils;
import com.augurit.gzcgwsp.commonex.util.lang.DateUtilsEX;
import com.augurit.gzcgwsp.printDoc.entity.YwPrintDoc;
import com.augurit.gzcgwsp.ywcmn.dao.YwComSjclDao;
import com.augurit.gzcgwsp.ywcmn.service.IYwComPrintService;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComPrintForm;
import com.augurit.wf.bi.service.IWfBusTemplateService;
import com.augurit.wf.bi.web.form.WfBusTemplateForm;
import com.augurit.wf.bi.web.form.WfTemplateAttachmentRefForm;

@Service
@Transactional
public class YwComDzgzService implements IYwComPrintService {
    @Autowired
    private YwComSjclDao entityDao;
    @Autowired
    private IWfBusTemplateService wfBusTemplateService;

    public Page<YwComPrintForm> getPrintDataList(Page<YwComPrintForm> page, YwComPrintForm form) {
        // 建立新的分页对象
        Page pg = PageUtils.newPageInstance(page);
        
        StringBuffer sql = new StringBuffer();
        sql.append("select t.* ");
        sql.append("  from (select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq, 1 optid from Yw_Rq_Ssgd_Sqb t where t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq, 2 optid from Yw_Rq_Qyzz_Info t where t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq, 3 optid from YW_JG_HWGG_SQB t where t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq, 4 optid from YW_RQ_QYJYXK t where t.branch='rqjyxkz_gdqy' and t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq, 5 optid from YW_RQ_QYJYXK t where t.branch='rqjyxkz_pzqy' and t.ysl is null) t ");
        sql.append(" where 1=1 ");
        
        // 查询条件
        if(form != null){
            if(form.getAcceptNo() != null && form.getAcceptNo().trim().length() > 0){
                sql.append(" and t.acceptNo like '%" + form.getAcceptNo() + "%'");
            }
            if(form.getTopic() != null && form.getTopic().trim().length() > 0){
                sql.append(" and t.topic like '%" + form.getTopic() + "%'");
            }
            if (form.getOptid() != null && form.getOptid().longValue() != 0) {
                sql.append(" and t.optid = " + form.getOptid());
            }
        }
        sql.append(" order by t.sarq desc ");

        if (pg.isAutoCount()) {
            BigDecimal totalCount = (BigDecimal) entityDao.getSession().createSQLQuery("select count(*) from (" + sql + ")").uniqueResult();
            pg.setTotalCount(totalCount.longValue());
        }
        SQLQuery q = entityDao.getSession().createSQLQuery(sql.toString());
        q.setFirstResult(pg.getFirst() - 1);
        q.setMaxResults(pg.getPageSize());

        List<WfBusTemplateForm> tplList = wfBusTemplateService.getAllActive();
        List<YwComPrintForm> formList = new ArrayList<YwComPrintForm>();
        
        List<Map<String, Object>> resultList = q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        for (Map<String, Object> dataMap : resultList) {
            Long id = ((BigDecimal) dataMap.get("ID")).longValue();
            String acceptNo = (String) dataMap.get("ACCEPTNO");
            String topic = (String) dataMap.get("TOPIC");
            Date sarq = (Date) dataMap.get("SARQ");
            Long optid = ((BigDecimal) dataMap.get("OPTID")).longValue();
            
            YwComPrintForm rowForm = new YwComPrintForm();
            rowForm.setId(id);
            rowForm.setAcceptNo(acceptNo);
            rowForm.setTopic(topic);
            rowForm.setSarq(DateUtils.dateTimeToString(sarq, DateUtils.YEAR_MOUTH_DAY_));
            rowForm.setOptid(optid);
            if (optid.longValue() == 1) {
                rowForm.setTemplateCode("rqssgd");
            }else if (optid.longValue() == 2) {
                rowForm.setTemplateCode("rqrsqjazwxqyzz");
            }else if (optid.longValue() == 3) {
                rowForm.setTemplateCode("hwggshsqb");
            }else if (optid.longValue() == 4) {
                rowForm.setTemplateCode("rqjyxkz_gdqy");
            }else if (optid.longValue() == 5) {
                rowForm.setTemplateCode("rqjyxkz_pzqy");
            }
            for (WfBusTemplateForm tplForm : tplList) {
                if (tplForm.getTemplateCode().equals(rowForm.getTemplateCode())) {
                    rowForm.setOptName(tplForm.getTemplateName());
                }
            }
            formList.add(rowForm);
        }
        PageUtils.copy(pg, formList, page);
        return page;
    }
    
    /**
	 * 分页查询需要受理通知书打印记录
	 * @param page
	 * @prama  page
	 * @prama  printForm
	 * @return 
	 */
    public Page<YwComPrintForm> getPrintAcceptDataList(Page<YwComPrintForm> page, YwComPrintForm form) {
        // 建立新的分页对象
        Page pg = PageUtils.newPageInstance(page);
        
        StringBuffer sql = new StringBuffer();
        sql.append("select t.* ");
        sql.append("  from (select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq,t.send_accept_notice sendAcceptNotice, 1 optid from Yw_Rq_Ssgd_Sqb t where t.send_accept_notice='1' and t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq,t.send_accept_notice sendAcceptNotice, 2 optid from Yw_Rq_Qyzz_Info t where t.send_accept_notice='1' and t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq,t.send_accept_notice sendAcceptNotice, 3 optid from YW_JG_HWGG_SQB t where t.send_accept_notice='1' and t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq,t.send_accept_notice sendAcceptNotice, 4 optid from YW_RQ_QYJYXK t where t.send_accept_notice='1' and t.branch='rqjyxkz_gdqy' and t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq,t.send_accept_notice sendAcceptNotice, 5 optid from YW_RQ_QYJYXK t where t.send_accept_notice='1' and t.branch='rqjyxkz_pzqy' and t.ysl is null) t ");
        sql.append(" where 1=1 ");
        
        // 查询条件
        if(form != null){
            if(form.getAcceptNo() != null && form.getAcceptNo().trim().length() > 0){
                sql.append(" and t.acceptNo like '%" + form.getAcceptNo() + "%'");
            }
            if(form.getTopic() != null && form.getTopic().trim().length() > 0){
                sql.append(" and t.topic like '%" + form.getTopic() + "%'");
            }
            if (form.getOptid() != null && form.getOptid().longValue() != 0) {
                sql.append(" and t.optid = " + form.getOptid());
            }
        }
        sql.append(" order by t.sarq desc ");

        if (pg.isAutoCount()) {
            BigDecimal totalCount = (BigDecimal) entityDao.getSession().createSQLQuery("select count(*) from (" + sql + ")").uniqueResult();
            pg.setTotalCount(totalCount.longValue());
        }
        SQLQuery q = entityDao.getSession().createSQLQuery(sql.toString());
        q.setFirstResult(pg.getFirst() - 1);
        q.setMaxResults(pg.getPageSize());

        List<WfBusTemplateForm> tplList = wfBusTemplateService.getAllActive();
        List<YwComPrintForm> formList = new ArrayList<YwComPrintForm>();
        
        List<Map<String, Object>> resultList = q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        for (Map<String, Object> dataMap : resultList) {
            Long id = ((BigDecimal) dataMap.get("ID")).longValue();
            String acceptNo = (String) dataMap.get("ACCEPTNO");
            String topic = (String) dataMap.get("TOPIC");
            Date sarq = (Date) dataMap.get("SARQ");
            Long optid = ((BigDecimal) dataMap.get("OPTID")).longValue();
            String sendAcceptNotice = (String) dataMap.get("SENDACCEPTNOTICE");
            
            YwComPrintForm rowForm = new YwComPrintForm();
            rowForm.setId(id);
            rowForm.setAcceptNo(acceptNo);
            rowForm.setTopic(topic);
            rowForm.setSarq(DateUtils.dateTimeToString(sarq, DateUtils.YEAR_MOUTH_DAY_));
            rowForm.setOptid(optid);
            rowForm.setSendAcceptNotice(sendAcceptNotice);
            if (optid.longValue() == 1) {
                rowForm.setTemplateCode("rqssgd");
            }else if (optid.longValue() == 2) {
                rowForm.setTemplateCode("rqrsqjazwxqyzz");
            }else if (optid.longValue() == 3) {
                rowForm.setTemplateCode("hwggshsqb");
            }else if (optid.longValue() == 4) {
                rowForm.setTemplateCode("rqjyxkz_gdqy");
            }else if (optid.longValue() == 5) {
                rowForm.setTemplateCode("rqjyxkz_pzqy");
            }
            for (WfBusTemplateForm tplForm : tplList) {
                if (tplForm.getTemplateCode().equals(rowForm.getTemplateCode())) {
                    rowForm.setOptName(tplForm.getTemplateName());
                }
            }
            formList.add(rowForm);
        }
        PageUtils.copy(pg, formList, page);
        return page;
    }
    
    
    /**
	 * 分页查询需要延期通知书打印记录
	 * @param page
	 * @prama  page
	 * @prama  printForm
	 * @return 
	 */
    public Page<YwComPrintForm> getPrintYqDataList(Page<YwComPrintForm> page, YwComPrintForm form) {
        // 建立新的分页对象
        Page pg = PageUtils.newPageInstance(page);
        
        StringBuffer sql = new StringBuffer();
        sql.append("select t.* ");
        sql.append("  from (select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq,t.send_yq_notice sendYqNotice, 1 optid from Yw_Rq_Ssgd_Sqb t where t.send_yq_notice='1' and t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq,t.send_yq_notice sendYqNotice, 2 optid from Yw_Rq_Qyzz_Info t where t.send_yq_notice='1' and t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq,t.send_yq_notice sendYqNotice, 3 optid from YW_JG_HWGG_SQB t where t.send_yq_notice='1' and t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq,t.send_yq_notice sendYqNotice, 4 optid from YW_RQ_QYJYXK t where t.send_yq_notice='1' and t.branch='rqjyxkz_gdqy' and t.ysl is null ");
        sql.append("        UNION ALL ");
        sql.append("        select t.id, t.accept_No acceptNo, t.sqdw topic, t.sarq,t.send_yq_notice sendYqNotice, 5 optid from YW_RQ_QYJYXK t where t.send_yq_notice='1' and t.branch='rqjyxkz_pzqy' and t.ysl is null) t ");
        sql.append(" where 1=1 ");
        
        // 查询条件
        if(form != null){
            if(form.getAcceptNo() != null && form.getAcceptNo().trim().length() > 0){
                sql.append(" and t.acceptNo like '%" + form.getAcceptNo() + "%'");
            }
            if(form.getTopic() != null && form.getTopic().trim().length() > 0){
                sql.append(" and t.topic like '%" + form.getTopic() + "%'");
            }
            if (form.getOptid() != null && form.getOptid().longValue() != 0) {
                sql.append(" and t.optid = " + form.getOptid());
            }
        }
        sql.append(" order by t.sarq desc ");

        if (pg.isAutoCount()) {
            BigDecimal totalCount = (BigDecimal) entityDao.getSession().createSQLQuery("select count(*) from (" + sql + ")").uniqueResult();
            pg.setTotalCount(totalCount.longValue());
        }
        SQLQuery q = entityDao.getSession().createSQLQuery(sql.toString());
        q.setFirstResult(pg.getFirst() - 1);
        q.setMaxResults(pg.getPageSize());

        List<WfBusTemplateForm> tplList = wfBusTemplateService.getAllActive();
        List<YwComPrintForm> formList = new ArrayList<YwComPrintForm>();
        
        List<Map<String, Object>> resultList = q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        for (Map<String, Object> dataMap : resultList) {
            Long id = ((BigDecimal) dataMap.get("ID")).longValue();
            String acceptNo = (String) dataMap.get("ACCEPTNO");
            String topic = (String) dataMap.get("TOPIC");
            Date sarq = (Date) dataMap.get("SARQ");
            Long optid = ((BigDecimal) dataMap.get("OPTID")).longValue();
            String sendYqNotice = (String) dataMap.get("SENDYQNOTICE");
            
            YwComPrintForm rowForm = new YwComPrintForm();
            rowForm.setId(id);
            rowForm.setAcceptNo(acceptNo);
            rowForm.setTopic(topic);
            rowForm.setSarq(DateUtils.dateTimeToString(sarq, DateUtils.YEAR_MOUTH_DAY_));
            rowForm.setOptid(optid);
            rowForm.setSendYqNotice(sendYqNotice);
            if (optid.longValue() == 1) {
                rowForm.setTemplateCode("rqssgd");
            }else if (optid.longValue() == 2) {
                rowForm.setTemplateCode("rqrsqjazwxqyzz");
            }else if (optid.longValue() == 3) {
                rowForm.setTemplateCode("hwggshsqb");
            }else if (optid.longValue() == 4) {
                rowForm.setTemplateCode("rqjyxkz_gdqy");
            }else if (optid.longValue() == 5) {
                rowForm.setTemplateCode("rqjyxkz_pzqy");
            }
            for (WfBusTemplateForm tplForm : tplList) {
                if (tplForm.getTemplateCode().equals(rowForm.getTemplateCode())) {
                    rowForm.setOptName(tplForm.getTemplateName());
                }
            }
            formList.add(rowForm);
        }
        PageUtils.copy(pg, formList, page);
        return page;
    }
    
    public Page<YwComPrintForm> getPrintTplList(Page<YwComPrintForm> page, YwComPrintForm form) {
        
        return null;
    }

    public Page<YwComPrintForm> search(Page<YwComPrintForm> page,
            List<PropertyFilter> filters) {
        // TODO Auto-generated method stub
        return null;
    }

    public Page<YwComPrintForm> search(Page<YwComPrintForm> page,
            YwComPrintForm form) {
        // TODO Auto-generated method stub
        return null;
    }

    public void delete(Long... ids) {
        // TODO Auto-generated method stub

    }

    public YwComPrintForm get(Long id) {
        // TODO Auto-generated method stub
        return null;
    }

    public void save(YwComPrintForm... forms) {
        // TODO Auto-generated method stub

    }

    /*
     * 将创建人和创建日期加入模版中 每个模版的filecode不同 不需要加模版id进去
     * @see com.augurit.gzcgwsp.ywcmn.service.IYwComPrintService#translateList(com.augurit.wf.bi.web.form.WfTemplateAttachmentRefForm, com.augurit.gzcgwsp.ywcmn.web.form.YwComPrintForm)
     */
    public void translateList(WfTemplateAttachmentRefForm reForm,
            YwComPrintForm form) {
        String sql=" from YwPrintDoc where ywId='"+form.getFkid()+"' and fileCode='"+reForm.getFileCode()+"'";//+"' and tempdocId='"+reForm.getId()+"'";
        List<YwPrintDoc> docs=this.entityDao.find(sql);
        if(docs!=null && docs.size()>0){
            YwPrintDoc doc=docs.get(0);
            reForm.setCreateTime(doc.getCreatDate());
            reForm.setCreator(doc.getCreator());
        }
    }

   
	public List<YwPrintDoc> findDoc(Long id, String fileCode, Long tempId) {
			String date="";
		  	String sql=" from YwPrintDoc where ywId='"+id+"' and fileCode='"+fileCode+"' and tempdocId='"+tempId+"'";
	        List<YwPrintDoc> docs=this.entityDao.find(sql);
		return docs;
	}

}
